package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.GraduateProjectSource;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;;

public class GraduateProjectSourceDao {
    private static GraduateProjectSourceDao graduateProjectSourceDao = null;

    public static GraduateProjectSourceDao getInstance() {
        if (graduateProjectSourceDao == null) {
            graduateProjectSourceDao = new GraduateProjectSourceDao();
        }
        return graduateProjectSourceDao;
    }

    public static Collection<GraduateProjectSource> findAll(Connection connection)
            throws SQLException {
        Collection<GraduateProjectSource> graduateProjectSources = new ArrayList<GraduateProjectSource>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection, "GraduateProjectSource");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM GraduateProjectSource ");

        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while (resultSet.next()) {
            graduateProjectSources.add(
                    new GraduateProjectSource(
                            resultSet.getInt("id"),
                            resultSet.getString("no"),
                            resultSet.getString("description"),
                            resultSet.getString("remarks")
            ));
        }
        JdbcHelper.close(resultSet, pstmt);
        return graduateProjectSources;
    }

    public static GraduateProjectSource find(int id, Connection connection) throws SQLException {

        //创建sql语句
        String find_sql = "select * from GraduateProjectSource where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        GraduateProjectSource graduateProjectSource = null;
        //游标下移
        if (resultSet.next()) {
            //创建GraduateProjectSource对象，各字段为结果集中相应字段
            graduateProjectSource = new GraduateProjectSource(resultSet.getInt("id"),
                    resultSet.getString("no"),
                    resultSet.getString("description"),
                    resultSet.getString("remarks")
            );
        }
        JdbcHelper.close(resultSet, pstmt);
        return graduateProjectSource;
    }
}
